USE EMODE_sauvegarde
go

alter database EMODE_sauvegarde
add Filegroup emode_part_1997
go

alter database EMODE_sauvegarde
add Filegroup emode_part_1998
go

alter database EMODE_sauvegarde
add Filegroup emode_part_1999
go

alter database EMODE_sauvegarde
add Filegroup emode_part_2000
go

alter database EMODE_sauvegarde
add Filegroup emode_part_2001
go

alter database EMODE_sauvegarde
add Filegroup emode_part_2010
go

alter database EMODE_sauvegarde
add Filegroup emode_part
go

alter database EMODE_sauvegarde
ADD FILE 
( 
NAME = emode_data_part_1997,
FILENAME = 'E:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\emode_data_part_1997.ndf',
SIZE = 2MB,
MAXSIZE = 50MB,
FILEGROWTH = 10MB
) TO FILEGROUP emode_part_1997;
GO

alter database EMODE_sauvegarde
ADD FILE 
( 
NAME = emode_data_part_1998,
FILENAME = 'E:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\emode_data_part_1998.ndf',
SIZE = 2MB,
MAXSIZE = 50MB,
FILEGROWTH = 10MB
) TO FILEGROUP emode_part_1998;
GO

alter database EMODE_sauvegarde
ADD FILE 
( 
NAME = emode_data_part_1999,
FILENAME = 'E:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\emode_data_part_1999.ndf',
SIZE = 2MB,
MAXSIZE = 50MB,
FILEGROWTH = 10MB
) TO FILEGROUP emode_part_1999;
GO

alter database EMODE_sauvegarde
ADD FILE 
( 
NAME = emode_data_part_2000,
FILENAME = 'E:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\emode_data_part_2000.ndf',
SIZE = 2MB,
MAXSIZE = 50MB,
FILEGROWTH = 10MB
) TO FILEGROUP emode_part_2000;
GO

alter database EMODE_sauvegarde
ADD FILE 
( 
NAME = emode_data_part_2001,
FILENAME = 'E:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\emode_data_part_2001.ndf',
SIZE = 2MB,
MAXSIZE = 50MB,
FILEGROWTH = 10MB
) TO FILEGROUP emode_part_2001;
GO

alter database EMODE_sauvegarde
ADD FILE 
( 
NAME = emode_data_part_2010,
FILENAME = 'E:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\emode_data_part_2010.ndf',
SIZE = 2MB,
MAXSIZE = 50MB,
FILEGROWTH = 10MB
) TO FILEGROUP emode_part_2010;
GO
	
alter database EMODE_sauvegarde
ADD FILE 
( 
NAME = emode_data_part,
FILENAME = 'E:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\emode_data_part.ndf',
SIZE = 2MB,
MAXSIZE = 50MB,
FILEGROWTH = 10MB
) TO FILEGROUP emode_part;
GO	


---------------------

USE EMODE_sauvegarde
GO

create partition function FoncPart (NUMERIC(5,0)) as range left for values (53,105, 157,209,262,314); 
GO

CREATE PARTITION SCHEME SchemaPart as partition FoncPart 
to (emode_part_1997,emode_part_1998,emode_part_1999,emode_part_2000,emode_part_2001,emode_part_2010, emode_part)
go

CREATE TABLE SHOP_FACTS_PART 
    (
     ID NUMERIC (5,0) NOT NULL , 
     ARTICLE_CODE NUMERIC (6,0) , 
     COLOR_CODE NUMERIC (4,0) , 
     WEEK_KEY NUMERIC (3,0), 
     SHOP_CODE NUMERIC (4,0), 
     MARGIN NUMERIC (18,0) , 
     AMOUNT_SOLD NUMERIC (13,2) , 
     QUANTITY_SOLD NUMERIC (13,2), 
     CONSTRAINT SHOP_FACTS_PART_PK PRIMARY KEY NONCLUSTERED (ID)
    )
    on SchemaPart(ID) 
GO 

ALTER TABLE SHOP_FACTS_PART 
ADD FOREIGN KEY(ARTICLE_CODE, COLOR_CODE)
REFERENCES ARTICLE_COLOR_LOOKUP (ARTICLE_CODE, COLOR_CODE);

ALTER TABLE SHOP_FACTS_PART  
ADD FOREIGN KEY(ARTICLE_CODE)
REFERENCES ARTICLE_LOOKUP (ARTICLE_CODE);

ALTER TABLE SHOP_FACTS_PART
ADD FOREIGN KEY(WEEK_KEY)
REFERENCES CALENDAR_YEAR_LOOKUP (WEEK_KEY);

ALTER TABLE SHOP_FACTS_PART  
ADD FOREIGN KEY(SHOP_CODE)
REFERENCES OUTLET_LOOKUP (SHOP_CODE);

insert  into SHOP_FACTS_PART
select * from SHOP_FACTS;

CREATE TABLE PRODUCT_PROMOTION_FACTS_PART 
    ( 
     ID NUMERIC (5,0)  NOT NULL , 
     ARTICLE_CODE NUMERIC (6,0) , 
     WEEK_KEY NUMERIC (3,0) , 
     DURATION NUMERIC (2,0) , 
     PROMOTION_KEY NUMERIC (2,0) , 
     PROMOTION_COST NUMERIC (18,0),
     CONSTRAINT PROD_PROM_FACTS_PART_PK PRIMARY KEY ( ID )
    )
    on SchemaPart(ID) 
go

ALTER TABLE PRODUCT_PROMOTION_FACTS_PART
ADD FOREIGN KEY(ARTICLE_CODE) 
REFERENCES ARTICLE_LOOKUP(ARTICLE_CODE);

ALTER TABLE PRODUCT_PROMOTION_FACTS_PART
ADD FOREIGN KEY(WEEK_KEY) 
REFERENCES CALENDAR_YEAR_LOOKUP(WEEK_KEY);  
 
insert into PRODUCT_PROMOTION_FACTS_PART
select * from PRODUCT_PROMOTION_FACTS;